#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Pg;
use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use uniquify;
use run_cmd;

use db_parameters;
use ortho_species;

print <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_closest_other_species

		Get the orthologous protein or gene with the lowest dS

888888888888888888888888888888888888888888888888888888888888888888888
HEADLINE




#
#	Connect to genome database
#
my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);
#my @db_param = get_db_parameters();

	#
	# Get lowest dS
	#
	print STDERR "\n\tGet dS for all pairs from different species...\n";
	my $cmd = <<"    CMD";
	seq_pairs_filter_by_regex
                            --regex "($ortho_prot1|$ortho_prot2)"
                            --patterns "$ortho_prot1"
                            --alt_patterns "$ortho_prot2"
                            --verbose
                            --verbose 
                            --input_file $dir_orthologs_output/ds_res.both_filtered
                            > $dir_pipeline_ds_temp/ortholog.ds
    CMD
	run_cmd($cmd);


	print STDERR "\n\tFor each protein, get pairs with the lowest dS...\n";
	$cmd = <<"    CMD";
	seq_pairs_lowest
							--reciprocals
							--verbose
                            --input_file $dir_pipeline_ds_temp/ortholog.ds
							 > $dir_pipeline_ds_temp/closest_other_species.ds
    CMD
	run_cmd($cmd);



	open DS, "$dir_pipeline_ds_temp/closest_other_species.ds" 
							or die "Error:\n\tCould not open $FindBin::Bin/lowest.ds\n";
							
	print STDERR "\n\tEntering into panda...\n";
	$dbh->do("CREATE TEMP TABLE t1 (prot_id TEXT, other_species_prot_id TEXT, ds REAL);");
	$dbh->do("COPY t1".
					"(prot_id, other_species_prot_id, dS) FROM STDIN") or die;
	$dbh->pg_putline($_) while (<DS>);
	$dbh->pg_endcopy;

	$dbh->do("DELETE FROM orthologs.closest_other_species_prot_id WHERE protocol_id = $curr_protocol_id");
	$dbh->do("VACUUM ANALYSE orthologs.closest_other_species_prot_id;");
	$dbh->do("INSERT INTO orthologs.closest_other_species_prot_id ".
			 "(prot_id, other_species_prot_id, dS, protocol_id)".
			 "SELECT prot_id, other_species_prot_id, dS, $curr_protocol_id FROM t1;");

	print STDERR "\n\tSetting orthologs.closest_other_species for gene_ids...\n";
	my $sql_cmd =<<"PL/SQLCMD";
	TRUNCATE TABLE orthologs.closest_other_species;

	INSERT
		INTO orthologs.closest_other_species
		(
			gene_id,
			prot_id,
			species,
			other_species_gene_id,
			other_species_prot_id,
			other_species,
            dS,
            protocol_id
		)
		SELECT
                DISTINCT ON (e1.gene_id)
    			e1.gene_id, 
                e1.prot_id,
                e1.species, 
                e2.gene_id AS other_species_gene_id, 
                e1.prot_id AS other_species_prot_id,
                e2.species AS other_species, 
                dS,
                protocol_id
            FROM
                orthologs.closest_other_species_prot_id NATURAL JOIN 
                ens_id e1 JOIN 
                ens_id e2 ON (e2.prot_id = other_species_prot_id)
            ORDER BY
                e1.gene_id, 
                dS;


	VACUUM ANALYSE orthologs.closest_other_species;
PL/SQLCMD
	$dbh->do($sql_cmd);
	


	
	print STDERR "\tDone\n";
	print STDERR "\tCompleted\n";
	unlink("$dir_pipeline_ds_temp/closest_other_species.ds");
	log_pipeline_stage($dbh, $curr_protocol_id, 32, 'table closest_other_species', $start_time);
	$dbh->disconnect();

